﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class PhieuXuatHangDAO
    {
        public List<PhieuXuatHangDTO> LaySoPhieuXuatHang()
        {
            string sql = "SELECT * FROM PhieuXuatHang WHERE Deleted = 0 ";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);

                SqlDataReader reader = cmd.ExecuteReader();
                List<PhieuXuatHangDTO> danhsach = new List<PhieuXuatHangDTO>();
                while (reader.Read())
                {
                    PhieuXuatHangDTO pxhDTO = new PhieuXuatHangDTO();

                    pxhDTO.SoPhieu = reader.GetString(0);
                    pxhDTO.MaHang = reader.GetString(1);
                    pxhDTO.MaKH = reader.GetString(2);
                    pxhDTO.SoLuong = reader.GetInt32(3);
                    pxhDTO.DonGia = reader.GetInt32(4);
                    pxhDTO.ThueSuat = reader.GetInt32(5);
                    pxhDTO.ThanhTien = reader.GetInt32(6);
                    pxhDTO.MaNV = reader.GetString(7);
                    pxhDTO.NgayXuat = reader.GetDateTime(8);
                    pxhDTO.GhiChu = reader.GetString(9);

                    danhsach.Add(pxhDTO);
                }
                conn.Close();
                return danhsach;
            }
        }
        public bool XoaPhieuXuatHang(string SoPhieu)
        {
            string sql = "UPDATE PhieuXuatHang SET Deleted = 1 WHERE SoPhieu = '" + SoPhieu + "'";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                int kq = cmd.ExecuteNonQuery();
                conn.Close();
                if (kq >= 0)
                    return true;
                return false;
            }
        }
        public bool ThemPhieuXuatHang(PhieuXuatHangDTO pxh)
        {
            string sql = "INSERT INTO PhieuXuatHang(SoPhieu, MaHang, MaKH, SoLuong, DonGia, ThueSuat, ThanhTien, MaNV, NgayXuat, GhiChu, Deleted)";
            sql += "VALUES('" + pxh.SoPhieu + "',N'" + pxh.MaHang + "', '" + pxh.MaKH + "', " + pxh.SoLuong + ", " + pxh.DonGia + ", " + pxh.ThueSuat + ", " + pxh.ThanhTien + ", '" + pxh.MaNV + "', '" + pxh.NgayXuat + "', N'" + pxh.GhiChu + "', 0)";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                int kq = cmd.ExecuteNonQuery();
                conn.Close();
                if (kq >= 0)
                    return true;
                return false;
            }
        }
        public bool KTSoPhieu(string SoPhieu)
        {
            string sql = "SELECT COUNT(*) FROM PhieuXuatHang WHERE SoPhieu='" + SoPhieu + "'";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader read = cmd.ExecuteReader();
                read.Read();
                int kq = read.GetInt32(0);
                conn.Close();
                if (kq == 0)
                    return true;
                return false;
            }

        }
        public PhieuXuatHangDTO LayPhieuTheoMa(string SoPhieu)
        {
            string sql = "SELECT * FROM PhieuXuatHang WHERE SoPhieu = '" + SoPhieu + "'";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);

                SqlDataReader reader = cmd.ExecuteReader();
                PhieuXuatHangDTO pxhDTO = new PhieuXuatHangDTO();
                reader.Read();
                pxhDTO.SoPhieu = reader.GetString(0);
                pxhDTO.MaHang = reader.GetString(1);
                pxhDTO.MaKH = reader.GetString(2);
                pxhDTO.SoLuong = reader.GetInt32(3);
                pxhDTO.DonGia = reader.GetInt32(4);
                pxhDTO.ThueSuat = reader.GetInt32(5);
                pxhDTO.ThanhTien = reader.GetInt32(6);
                pxhDTO.MaNV = reader.GetString(7);
                pxhDTO.NgayXuat = reader.GetDateTime(8);
                pxhDTO.GhiChu = reader.GetString(9);
                conn.Close();
                return pxhDTO;
            }
        }
        public bool SuaPhieuXuatHang(PhieuXuatHangDTO pxh)
        {
            string sql = "UPDATE PhieuXuatHang SET MaHang = '" + pxh.MaHang + "', MaKH = '" + pxh.MaKH + "', ";
            sql += "SoLuong = " + pxh.SoLuong + ", DonGia = " + pxh.DonGia + ", ThueSuat = " + pxh.ThueSuat + ", ThanhTien = " + pxh.ThanhTien + ",";
            sql += " MaNV = '" + pxh.MaNV + "', NgayXuat = '" + pxh.NgayXuat + "', GhiChu = '" + pxh.GhiChu + "' WHERE SoPhieu = '" + pxh.SoPhieu + "'";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                int kq = cmd.ExecuteNonQuery();
                conn.Close();
                if (kq >= 0)
                    return true;
                return false;
            }
        }

        public int ThongKeBan(string mahang, DateTime ngaytu, DateTime ngayden)
        {
            string sql = "SELECT SUM(SoLuong) FROM PhieuXuatHang WHERE Mahang = '" + mahang + "'" +
                "and NgayXuat between '" + ngaytu.ToShortDateString() + "' and '" + ngayden.ToShortDateString() +"'";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);

                SqlDataReader reader = cmd.ExecuteReader();
                int sum = 0;
                reader.Read();
                sum = reader.GetInt32(0);
                conn.Close();
                return sum;
            }
        }
    }
}
